package com.fs.ibox.dao.impl;

import com.fs.ibox.dao.UserDao;
import com.fs.ibox.pojo.User;
import com.fs.ibox.util.JDBCUtil;
import com.fs.ibox.vo.ShowcollVO;
import com.fs.ibox.vo.UserCollection;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserDaoimpl implements UserDao {


    @Override
    public User querNameAndPasswprd(User user) {
        String sql = "SELECT * FROM tb_user WHERE username=? and password=?";
        List<User> users = JDBCUtil.executeQuery(sql, User.class, user.getUserName(), user.getPassword());
        return users == null || users.isEmpty()?null:users.get(0) ;
    }

    @Override
    public int inserUserNameAndPassword(User user) {
        String sql = "INSERT INTO tb_user(username,password,state,money,num) VALUES (?,?,?,?,?)";
        return JDBCUtil.executeUpdate(sql, user.getUserName(), user.getPassword(), user.getState(), user.getMoney(), user.getNum());
    }

    @Override
    public Double queryMoneyByUser(String username) {
        String sql = "SELECT * FROM tb_user WHERE username = ?";
        List<User> doubles = JDBCUtil.executeQuery(sql, User.class, username);
        System.out.println(doubles);
        return  doubles.get(0).getMoney();
    }

    @Override
    public Double updateMoney(String username, double price) {
        Double money = queryMoneyByUser(username);
        Double newMoney = money - price;
        String sql = "UPDATE tb_user SET money = ? WHERE username = ?";
        JDBCUtil.executeUpdate(sql, newMoney, username);
        return newMoney;
    }
    @Override
    public Double earn(String username, double price) {
        Double money = queryMoneyByUser(username);
        Double newMoney = money + price;
        String sql = "UPDATE tb_user SET money = ? WHERE username = ?";
        JDBCUtil.executeUpdate(sql, newMoney, username);
        return newMoney;
    }
    @Override
    public ShowcollVO queryUserCollectionDetails(String username, String cname) {
        String sql = "SELECT id.cname,cid,cimg,releasenum,content,nickname FROM (tb_idcollection id LEFT JOIN tb_collection coll ON id.cname=coll.cname) LEFT JOIN tb_user us ON us.username=id.username WHERE id.cname=? AND us.username=? AND cstate=2;";

        Connection connection = null;
        PreparedStatement psmt = null;
        ResultSet rs = null;
        List<UserCollection>  list= new ArrayList<UserCollection>();
        ShowcollVO showcollVO = new ShowcollVO();
        try {
            connection = JDBCUtil.getConnection();
            psmt = connection.prepareStatement(sql);
            psmt.setString(1,cname);
            psmt.setString(2,username);
            rs = psmt.executeQuery();

//            List<String> list1 = new ArrayList<>();
            int count = 0;
            while (rs.next()){
                //循环一次创建一个String
                String cid = null;
                if (count == 0){
                    //给单属性赋值
                    showcollVO.setCname(rs.getString("cname"));
                    showcollVO.setCimg(rs.getString("cimg"));
                    showcollVO.setReleasenum(rs.getInt("releasenum"));
                    showcollVO.setContent(rs.getString("content"));
                    showcollVO.setNickname(rs.getString("nickname"));
                }
                cid=rs.getString("cid");
                showcollVO.getLists().add(cid);

            }
        } catch (SQLException e) {
            e.printStackTrace();
        }


        return showcollVO;
    }

    /**
     * 卖出藏品，更新藏品id表里，对应的藏品状态为1，变成售卖中
     * (发布0，在售中1，收藏中2)
     * @param cname
     * @param cid
     * @return
     */
    @Override
    public int updateInIdCollectionCstate(String cname, String cid,double price) {
        String sql = "UPDATE tb_idcollection SET cstate = 1,price=? WHERE cname=? AND cid=?";
        return JDBCUtil.executeUpdate(sql,price,cname,cid);
    }

    @Override
    public int update(User user) {
        String sql = "UPDATE tb_user SET nickname=? ,headportrait=? WHERE username=?";
        return JDBCUtil.executeUpdate(sql, user.getNickName(),user.getHeadportrait(), user.getUserName());
    }

    @Override
    public List<User> queryByusername(String username) {
        String sql="SELECT * FROM tb_user WHERE username=?";
        List<User> users = JDBCUtil.executeQuery(sql, User.class, username);
        return users;
    }

    @Override
    public void DeductionsByUsername(String username,double price) {
        String sql="UPDATE tb_user set money=money-? WHERE username=?";
        int i = JDBCUtil.executeUpdate(sql, price, username);
    }

}
